﻿-- HEADER set start dates for existing ideas

DECLARE @ideaIds TABLE([Id] UNIQUEIDENTIFIER NOT NULL)

DECLARE @curIdeaId UNIQUEIDENTIFIER

DECLARE @dates TABLE([Date] DATETIME NOT NULL)

DECLARE @curMinDate DATETIME

INSERT INTO @ideaIds
SELECT [Id] FROM [iPerform].[Idea]

DECLARE ideaIdCurs CURSOR FOR SELECT Id FROM @ideaIds;

OPEN ideaIdCurs

FETCH NEXT FROM ideaIdCurs INTO @curIdeaId

WHILE @@FETCH_STATUS = 0
BEGIN
	-- insert trade dates
	INSERT INTO @dates 
	SELECT t.[Date] 
	FROM [iPerform].[Idea] i
	INNER JOIN [iPerform].[IdeaToTrade] itt ON i.Id = itt.IdeaId
	INNER JOIN [TradingData].[Trade] t ON t.Id = itt.TradeId
	WHERE i.Id = @curIdeaId

	-- insert template dates
	INSERT INTO @dates 
	SELECT ta.[AddedDt] 
	FROM [iPerform].[Idea] i
	INNER JOIN [iPerform].[IdeaToTemplateAnswer] ita ON i.Id = ita.IdeaId
	INNER JOIN [iPerform].[TemplateAnswer] ta ON ta.Id = ita.TemplateAnswerId
	WHERE i.Id = @curIdeaId

	-- choose StartDate
	SELECT @curMinDate = ISNULL(MIN([Date]), GETDATE()) FROM @dates

	UPDATE [iPerform].[Idea] SET StartDate = @curMinDate WHERE Id = @curIdeaId

	SET @curMinDate = NULL
	DELETE FROM @dates

	FETCH NEXT FROM ideaIdCurs INTO @curIdeaId
END

CLOSE ideaIdCurs
DEALLOCATE ideaIdCurs

GO

ALTER TABLE [iPerform].[Idea]
ALTER COLUMN [StartDate] DATETIME NOT NULL